using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace eBirdAttend
{
    public partial class Holidays : UserControl
    {
        public string strCon = Properties.Settings.Default["ConString"].ToString();
        SqlConnection sqlCon = new SqlConnection();
        SqlCommand sqlCmd = new SqlCommand();

        public Holidays()
        {
            InitializeComponent();
            sqlCon.ConnectionString = strCon;
            sqlCmd.Connection = sqlCon;
        }

        private void chkAllSun_CheckedChanged(object sender, EventArgs e)
        {
            if (chkAllSun.Checked)
            {
                chkFirstSun.Checked = true;
                chkSecSun.Checked = true;
                chkThirdSun.Checked = true;
                chkFourthSun.Checked = true;
                chkFifthSun.Checked = true;

                chkFirstSun.Enabled = false;
                chkSecSun.Enabled = false;
                chkThirdSun.Enabled = false;
                chkFourthSun.Enabled = false;
                chkFifthSun.Enabled = false;  
            }
            else
            {
                chkFirstSun.Checked = false;
                chkSecSun.Checked = false;
                chkThirdSun.Checked = false;
                chkFourthSun.Checked = false;
                chkFifthSun.Checked = false;

                chkFirstSun.Enabled = true;
                chkSecSun.Enabled = true;
                chkThirdSun.Enabled = true;
                chkFourthSun.Enabled = true;
                chkFifthSun.Enabled = true;  
            }
        }

        private void chkAllMon_CheckedChanged(object sender, EventArgs e)
        {
            if (chkAllMon.Checked)
            {
                chkFirstMon.Checked = true;
                chkSecMon.Checked = true;
                chkThirdMon.Checked = true;
                chkFourthMon.Checked = true;
                chkFifthMon.Checked = true;

                chkFirstMon.Enabled = false;
                chkSecMon.Enabled = false;
                chkThirdMon.Enabled = false;
                chkFourthMon.Enabled = false;
                chkFifthMon.Enabled = false;
            }
            else
            {
                chkFirstMon.Checked = false;
                chkSecMon.Checked = false;
                chkThirdMon.Checked = false;
                chkFourthMon.Checked = false;
                chkFifthMon.Checked = false;

                chkFirstMon.Enabled = true;
                chkSecMon.Enabled = true;
                chkThirdMon.Enabled = true;
                chkFourthMon.Enabled = true;
                chkFifthMon.Enabled = true;
            }
        }

        private void chkAllTue_CheckedChanged(object sender, EventArgs e)
        {
            if (chkAllTue.Checked)
            {
                chkFirstTue.Checked = true;
                chkSecTue.Checked = true;
                chkThirdTue.Checked = true;
                chkFourthTue.Checked = true;
                chkFifthTue.Checked = true;

                chkFirstTue.Enabled = false;
                chkSecTue.Enabled = false;
                chkThirdTue.Enabled = false;
                chkFourthTue.Enabled = false;
                chkFifthTue.Enabled = false;
            }
            else
            {
                chkFirstTue.Checked = false;
                chkSecTue.Checked = false;
                chkThirdTue.Checked = false;
                chkFourthTue.Checked = false;
                chkFifthTue.Checked = false;

                chkFirstTue.Enabled = true;
                chkSecTue.Enabled = true;
                chkThirdTue.Enabled = true;
                chkFourthTue.Enabled = true;
                chkFifthTue.Enabled = true;
            }
        }

        private void chkAllWed_CheckedChanged(object sender, EventArgs e)
        {
            if (chkAllWed.Checked)
            {
                chkFirstWed.Checked = true;
                chkSecWed.Checked = true;
                chkThirdWed.Checked = true;
                chkFourthWed.Checked = true;
                chkFifthWed.Checked = true;

                chkFirstWed.Enabled = false;
                chkSecWed.Enabled = false;
                chkThirdWed.Enabled = false;
                chkFourthWed.Enabled = false;
                chkFifthWed.Enabled = false;
            }
            else
            {
                chkFirstWed.Checked = false;
                chkSecWed.Checked = false;
                chkThirdWed.Checked = false;
                chkFourthWed.Checked = false;
                chkFifthWed.Checked = false;

                chkFirstWed.Enabled = true;
                chkSecWed.Enabled = true;
                chkThirdWed.Enabled = true;
                chkFourthWed.Enabled = true;
                chkFifthWed.Enabled = true;
            }
        }

        private void chkAllThur_CheckedChanged(object sender, EventArgs e)
        {
            if (chkAllThur.Checked)
            {
                chkFirstThur.Checked = true;
                chkSecThur.Checked = true;
                chkThirdThur.Checked = true;
                chkFourthThur.Checked = true;
                chkFifthThur.Checked = true;

                chkFirstThur.Enabled = false;
                chkSecThur.Enabled = false;
                chkThirdThur.Enabled = false;
                chkFourthThur.Enabled = false;
                chkFifthThur.Enabled = false;
            }
            else
            {
                chkFirstThur.Checked = false;
                chkSecThur.Checked = false;
                chkThirdThur.Checked = false;
                chkFourthThur.Checked = false;
                chkFifthThur.Checked = false;

                chkFirstThur.Enabled = true;
                chkSecThur.Enabled = true;
                chkThirdThur.Enabled = true;
                chkFourthThur.Enabled = true;
                chkFifthThur.Enabled = true;
            }
        }

        private void chkAllFri_CheckedChanged(object sender, EventArgs e)
        {
            if (chkAllFri.Checked)
            {
                chkFirstFri.Checked = true;
                chkSecFri.Checked = true;
                chkThirdFri.Checked = true;
                chkFourthFri.Checked = true;
                chkFifthFri.Checked = true;

                chkFirstFri.Enabled = false;
                chkSecFri.Enabled = false;
                chkThirdFri.Enabled = false;
                chkFourthFri.Enabled = false;
                chkFifthFri.Enabled = false;
            }
            else
            {
                chkFirstFri.Checked = false;
                chkSecFri.Checked = false;
                chkThirdFri.Checked = false;
                chkFourthFri.Checked = false;
                chkFifthFri.Checked = false;

                chkFirstFri.Enabled = true;
                chkSecFri.Enabled = true;
                chkThirdFri.Enabled = true;
                chkFourthFri.Enabled = true;
                chkFifthFri.Enabled = true;
            }
        }

        private void chkAllSat_CheckedChanged(object sender, EventArgs e)
        {
            if (chkAllSat.Checked)
            {
                chkFirstSat.Checked = true;
                chkSecSat.Checked = true;
                chkThirdSat.Checked = true;
                chkFourthSat.Checked = true;
                chkFifthSat.Checked = true;

                chkFirstSat.Enabled = false;
                chkSecSat.Enabled = false;
                chkThirdSat.Enabled = false;
                chkFourthSat.Enabled = false;
                chkFifthSat.Enabled = false;
            }
            else
            {
                chkFirstSat.Checked = false;
                chkSecSat.Checked = false;
                chkThirdSat.Checked = false;
                chkFourthSat.Checked = false;
                chkFifthSat.Checked = false;

                chkFirstSat.Enabled = true;
                chkSecSat.Enabled = true;
                chkThirdSat.Enabled = true;
                chkFourthSat.Enabled = true;
                chkFifthSat.Enabled = true;
            }
        }

        public void InsertAllDays()
        {
            DateTime FromDate = Convert.ToDateTime("01/01/" + cmbYearFrom.SelectedItem.ToString());
            DateTime ToDate = Convert.ToDateTime("31/12/" + cmbYearTo.SelectedItem.ToString());

            sqlCmd.CommandText = "InsertHolidays";
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCon.Open();

            if (chkAllSun.Checked)
            {
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", "SUNDAY");
                sqlCmd.ExecuteNonQuery();
            }
            if (chkAllMon.Checked)
            {
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", "MONDAY");
                sqlCmd.ExecuteNonQuery();
            }
            if (chkAllTue.Checked)
            {
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", "TUESDAY");
                sqlCmd.ExecuteNonQuery();
            }
            if (chkAllWed.Checked)
            {
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", "WEDNESDAY");
                sqlCmd.ExecuteNonQuery();
            }
            if (chkAllThur.Checked)
            {
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", "THURSDAY");
                sqlCmd.ExecuteNonQuery();
            }
            if (chkAllFri.Checked)
            {
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", "FRIDAY");
                sqlCmd.ExecuteNonQuery();
            }
            if (chkAllSat.Checked)
            {
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", "SATURDAY");
                sqlCmd.ExecuteNonQuery();
            }
            sqlCon.Close();
        }

        public void InsertAlternateDays()
        {
            //alternate days
            DateTime FromDate = Convert.ToDateTime("01/01/" + cmbYearFrom.SelectedItem.ToString());
            DateTime ToDate = Convert.ToDateTime("31/12/" + cmbYearTo.SelectedItem.ToString());

            sqlCmd.CommandText = "InsertHolidays";
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCon.Open();

            int nDay = 0;
            string strDay = "";

            if (chkFirstSun.Checked)
            {
                strDay = "SUNDAY";
                nDay = 1;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFirstMon.Checked)
            {
                strDay = "MONDAY";
                nDay = 1;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFirstTue.Checked)
            {
                strDay = "TUESDAY";
                nDay = 1;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFirstWed.Checked)
            {
                strDay = "WEDNESDAY";
                nDay = 1;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFirstThur.Checked)
            {
                strDay = "THURSDAY";
                nDay = 1;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFirstFri.Checked)
            {
                strDay = "FRIDAY";
                nDay = 1;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFirstSat.Checked)
            {
                strDay = "SATURDAY";
                nDay = 1;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            /////////////////////////////////////////////////////////////////////////
            if (chkSecSun.Checked)
            {
                strDay = "SUNDAY";
                nDay = 2;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkSecMon.Checked)
            {
                strDay = "MONDAY";
                nDay = 2;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkSecTue.Checked)
            {
                strDay = "TUESDAY";
                nDay = 2;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkSecWed.Checked)
            {
                strDay = "WEDNESDAY";
                nDay = 2;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkSecThur.Checked)
            {
                strDay = "THURSDAY";
                nDay = 2;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkSecFri.Checked)
            {
                strDay = "FRIDAY";
                nDay = 2;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkSecSat.Checked)
            {
                strDay = "SATURDAY";
                nDay = 2;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            ///////////////////////////////////////////////////////////////////////////////////////
            if (chkThirdSun.Checked)
            {
                strDay = "SUNDAY";
                nDay = 3;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkThirdMon.Checked)
            {
                strDay = "MONDAY";
                nDay = 3;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkThirdTue.Checked)
            {
                strDay = "TUESDAY";
                nDay = 3;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkThirdWed.Checked)
            {
                strDay = "WEDNESDAY";
                nDay = 3;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkThirdThur.Checked)
            {
                strDay = "THURSDAY";
                nDay = 3;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkThirdFri.Checked)
            {
                strDay = "FRIDAY";
                nDay = 3;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkThirdSat.Checked)
            {
                strDay = "SATURDAY";
                nDay = 3;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            ////////////////////////////////////////////////////////////////////////

            if (chkFourthSun.Checked)
            {
                strDay = "SUNDAY";
                nDay = 4;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFourthMon.Checked)
            {
                strDay = "MONDAY";
                nDay = 4;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFourthTue.Checked)
            {
                strDay = "TUESDAY";
                nDay = 4;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFourthWed.Checked)
            {
                strDay = "WEDNESDAY";
                nDay = 4;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFourthThur.Checked)
            {
                strDay = "THURSDAY";
                nDay = 4;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFourthFri.Checked)
            {
                strDay = "FRIDAY";
                nDay = 4;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFourthSat.Checked)
            {
                strDay = "SATURDAY";
                nDay = 4;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }

            /////////////////////////////////////////////////////////////

            if (chkFifthSun.Checked)
            {
                strDay = "SUNDAY";
                nDay = 5;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFifthMon.Checked)
            {
                strDay = "MONDAY";
                nDay = 5;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFifthTue.Checked)
            {
                strDay = "TUESDAY";
                nDay = 5;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFifthWed.Checked)
            {
                strDay  = "WEDNESDAY";
                nDay = 5;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFifthThur.Checked)
            {
                strDay = "THURSDAY";
                nDay = 5;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFifthFri.Checked)
            {
                strDay = "FRIDAY";
                nDay = 5;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            if (chkFifthSat.Checked)
            {
                strDay = "SATURDAY";
                nDay = 5;
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("@Startdate", FromDate);
                sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
                sqlCmd.Parameters.AddWithValue("@dayin", strDay);
                sqlCmd.Parameters.AddWithValue("@dayno", nDay);
                sqlCmd.ExecuteNonQuery();
            }
            sqlCon.Close();
        }

        public void InsertOtherHolidays()
        {
            DateTime FromDate = dateFrom.Value.Date;
            DateTime ToDate = dateTo.Value.Date;

            sqlCmd.CommandText = "InsertHolidays";
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCon.Open();

            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@StartDate", FromDate);
            sqlCmd.Parameters.AddWithValue("@EndDate", ToDate);
            sqlCmd.Parameters.AddWithValue("description", txtHoliName.Text);

            sqlCmd.ExecuteNonQuery();
            sqlCon.Close();
        }

        private void btnApply_Click(object sender, EventArgs e)
        {
            if (Convert.ToInt32(cmbYearFrom.SelectedItem) > Convert.ToInt32(cmbYearTo.SelectedItem))
            {
                MessageBox.Show("From year cannot be greater than to year");
            }
            else
            {
                InsertAllDays();
                InsertAlternateDays();               
            }
            FillDataGrid();          
        }

        private void Holidays_Load(object sender, EventArgs e)
        {
            cmbYearTo.SelectedIndex = 0;
            cmbYearFrom.SelectedIndex = 0;
            cmbYearFrom.Text = DateTime.Now.Year.ToString();
            cmbYearTo.Text = DateTime.Now.Year.ToString();
            FillDataGrid();
            rbAlternative.Checked = true;
            rbNewHolidays.Checked = false;
            groupBox1.Enabled = true;
            groupBox2.Enabled = false;

        }

        private void FillDataGrid()
        {
            sqlCmd.CommandText = "DisplayHolidays";
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCon.Open();

            sqlCmd.Parameters.Clear();
            sqlCmd.ExecuteNonQuery();
            DataSet ds = new DataSet();         
            SqlDataAdapter sqladap = new SqlDataAdapter(sqlCmd);           
            sqladap.Fill(ds);
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = ds.Tables[0].ToString();
            sqlCon.Close();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0)
            {
                if (DialogResult.Yes == MessageBox.Show("Are you sure you want to delete", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                {
                    //delete
                    sqlCmd.CommandText = "DeleteHolidays";
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCon.Open();

                    sqlCmd.Parameters.Clear();
                    sqlCmd.Parameters.AddWithValue("@date", Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[2].Value.ToString()));
                    sqlCmd.Parameters.AddWithValue("description", dataGridView1.SelectedRows[0].Cells[1].Value.ToString());

                    sqlCmd.ExecuteNonQuery();
                    sqlCon.Close();
                    FillDataGrid();
                }
            }
        }

        private void rbAlternative_CheckedChanged(object sender, EventArgs e)
        {
            if (rbAlternative.Checked)
            {
                groupBox2.Enabled = false;
                groupBox1.Enabled = true;
            }
        }

        private void rbNewHolidays_CheckedChanged(object sender, EventArgs e)
        {
            if (rbNewHolidays.Checked)
            {
                groupBox1.Enabled = false;
                groupBox2.Enabled = true;
                txtHoliName.Enabled = false;
                btnAdd.Enabled = true;
                btnSave.Enabled = false;
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            if (txtHoliName.Text != "")
            {
                InsertOtherHolidays();
                FillDataGrid();
                txtHoliName.Text = "";
                btnAdd.Enabled = true;
                btnSave.Enabled = false;
                txtHoliName.Enabled = false;
            }
            else
            {
                MessageBox.Show("Enter the holiday name");
                txtHoliName.Focus();
                btnSave.Enabled = true;
                btnAdd.Enabled = false;
            }           
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            txtHoliName.Enabled = true;        
            btnAdd.Enabled = false;
            btnSave.Enabled = true;
            txtHoliName.Focus();
        }

        private void btnDeleteAll_Click(object sender, EventArgs e)
        {
            if (DialogResult.Yes == MessageBox.Show("Are you sure you want to delete all", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
            {
                //delete
                sqlCmd.CommandText = "delete from holidays";
                sqlCmd.CommandType = CommandType.Text;
                sqlCon.Open();

                sqlCmd.Parameters.Clear();

                sqlCmd.ExecuteNonQuery();
                sqlCon.Close();
                FillDataGrid();
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Visible = false;
        }
    }
}
